package cn.big.bigd.biocode.dao;

import cn.big.bigd.biocode.model.Tool;
import cn.big.bigd.biocode.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 *@Author wangfan
 *@Date 2017/12/4 12:59
 */


@Repository
public interface ToolDAO extends JpaRepository<Tool, Integer>, JpaSpecificationExecutor<Tool> {

    List<Tool> findByUser(User user);


    @Modifying
    @Transactional
    @Query(value = "INSERT INTO tools_vocabularies(vocabulary_id, tool_id) VALUES(?1, ?2)", nativeQuery = true)
    void saveVTRelation(Integer vId, Integer tId);


    @Modifying
    @Transactional
    @Query(value = "INSERT INTO tools_publications(publication_id, tool_id) VALUES(?1, ?2)", nativeQuery = true)
    void savePTRelation(Integer pId, Integer tId);
    @Query(value = "select 1 FROM tools_publications WHERE publication_id=?1 AND tool_id=?2", nativeQuery = true)
    Integer findPTRelation(Integer pId, Integer tId);



    @Modifying
    @Transactional
    @Query(value = "INSERT INTO categories_tools(category_id, tool_id) VALUES(?1, ?2)", nativeQuery = true)
    void saveCTRelation(Integer cId, Integer tId);



    @Query(value = "SELECT co from (SELECT credits_roles.role_id, count(*) AS co FROM credits INNER JOIN credits_roles ON credits_roles.credit_id=credits.id WHERE credits.user_id=?1 GROUP BY credits_roles.role_id) a WHERE a.role_id=?2" ,nativeQuery = true)
    Integer findContributedToolsByCreditRole(Integer uId, Integer croleId);
}
